package hr.dao;

import java.sql.Types;
import java.util.ArrayList;

import javax.faces.model.SelectItem;

import com.sun.xml.internal.ws.org.objectweb.asm.Type;

import resources.bean.Login;
import resources.dao.DatabaseDAO;
import resources.pojo.hr_designation;
import resources.util.Tool;

public class DesignationDAO extends DatabaseDAO{
	public static String action(String action,hr_designation designation,int last){
		String sql = "call sp_hr_designation(?,?,?,?,?,?,?,?)";
		String result = "";
		try {
			getDBPoolConnection();
			conn.setAutoCommit(false);
			cs = conn.prepareCall(sql);
			cs.setString(1,action);
			cs.registerOutParameter(2,Types.VARCHAR);
			cs.setInt(3,last);
			cs.setString(4,designation.getId());
			cs.setString(5,designation.getName());
			cs.setString(6,designation.getDescription());
			cs.setInt(7,designation.getStatus());
			cs.setString(8, Login.user.getUsername());
			cs.executeUpdate();
			result = cs.getString(2);			
			conn.commit();			
		} catch (Exception e) {
			try {
				conn.rollback();
			} catch (Exception ex) {
				ex.printStackTrace();
			}
			e.printStackTrace();
			return result;
		} finally {
			closeConnection();
		}
		return result;
	}
	
	public static ArrayList<hr_designation> getListDesignationPaging(String name,int page, int row){
		ArrayList<hr_designation> temp;
		String sql = "call sp_hr_designation_paging(?,?,?,?)";
		try {
			getDBPoolConnection();
			conn.setAutoCommit(false);
			cs = conn.prepareCall(sql);
			cs.setString(1,"%"+name+"%");
			cs.setInt(2,page);
			cs.setInt(3,row);
			cs.registerOutParameter(4, Type.INT);
			rs = cs.executeQuery();
			temp = new ArrayList<hr_designation>();
			hr_designation item = null;
			while(rs.next()){
				item = new hr_designation();
				item.setId(rs.getString("d.id"));
				item.setName(rs.getString("d.name"));
				item.setDescription(rs.getString("d.description"));
				item.setCreated_by(rs.getString("d.created_by"));
				item.setCreated_date(rs.getTimestamp("d.created_date"));
				item.setModified_by(rs.getString("d.modified_by"));
				item.setModified_date(rs.getTimestamp("d.modified_date"));
				temp.add(item);
			}
			if(temp.size() > 0){
				Tool.page = cs.getInt(4);
			}
			conn.commit();
		} catch (Exception e) {
			try {
				conn.rollback();
			} catch (Exception ex) {
				ex.printStackTrace();
			}
			e.printStackTrace();
			return null;
		} finally {
			closeConnection();
		}
		return temp;
	}
	
	public static ArrayList<SelectItem> getDesignationSelect(){
		ArrayList<SelectItem> temp = new ArrayList<SelectItem>();
		String sql = "call sp_hr_designation_select(?,?)";
		try {
			getDBPoolConnection();
			conn.setAutoCommit(false);
			cs = conn.prepareCall(sql);
			cs.setString(1,"select_item");
			cs.setString(2,"");
			rs = cs.executeQuery();
			while(rs.next()){
				temp.add(new SelectItem(rs.getString("id"),rs.getString("name")));
			}
			conn.commit();
		} catch (Exception e) {
			try {
				conn.rollback();
			} catch (Exception ex) {
				ex.printStackTrace();
			}
			e.printStackTrace();
			return null;
		} finally {
			closeConnection();
		}
		return temp;
	}
}
